﻿CREATE PROCEDURE TimKiemSanPhamTheoTen
    @TenSanPham nvarchar(50),
    @LoaiSanPham nvarchar(50)
AS 
	Select tb.MaTinBan, tb.TieuDeTinBan, sp.TenSanPham, tb.GiaBan, tb.TinhTrangTinBan
	From SanPham sp, TinBan tb 
	Where sp.MaSanPham = tb.MaSanPham and sp.LoaiSanPham = @LoaiSanPham and sp.TenSanPham LIKE '%' + @TenSanPham +'%' and tb.TinhTrangTinBan = N'Còn hạn'
GO

CREATE PROCEDURE KiemTraDangNhap
@TenTaiKhoan VARCHAR(20),
@MatKhau VARCHAR(20)
AS
	SELECT MaKhachHang, HoTen
	FROM dbo.KhachHang
	WHERE TenTaiKhoan = @TenTaiKhoan AND MatKhau = @MatKhau
GO

CREATE PROCEDURE HienThiChiTietTinBan
@MaTinBan VARCHAR(10)
AS
	SELECT tb.MaTinBan, tb.TieuDeTinBan, sp.TenSanPham, sp.MoTaChiTiet, tb.GiaBan,tb.HinhThucBan, vc.LoaiVanChuyen, vc.PhiVanChuyen, tb.AnhDaiDien
	FROM dbo.SanPham sp, dbo.TinBan tb, dbo.HinhThucVanChuyen vc
	WHERE tb.MaTinBan = @MaTinBan AND sp.MaSanPham = tb.MaSanPham AND vc.LoaiVanChuyen = tb.LoaiVanChuyen
GO

CREATE PROCEDURE ThongTinKhachHang
@MaKhachHang VARCHAR(10)
AS
	SELECT HoTen, DienThoai, Email, DiaChi 
	FROM dbo.KhachHang
	WHERE MaKhachHang = @MaKhachHang
GO

CREATE PROCEDURE MuaNgaySanPham
@MaDonHang VARCHAR(10), 
@MaTinBan CHAR(10), 
@MaTaiKhoanMua CHAR(10), 
@DonGia FLOAT, 
@SoLuongMua INT, 
@ThanhTien FLOAT
AS
	-- them don hang
	Insert Into DonHang (MaDonHang, MaTinBan, MaTaiKhoanMua, DonGia, SoLuongMua, ThanhTien) 
	Values (@MaDonHang, @MaTinBan, @MaTaiKhoanMua, @DonGia, @SoLuongMua, @ThanhTien)
	-- cap nhat tinh trang tin ban
	UPDATE dbo.TinBan SET TinhTrangTinBan = N'Đã bán' WHERE MaTinBan = @MaTinBan
GO

-----------------------------------------------------------------------------------------------------------------
--KICH BAN 3-------------------------------------------------------------------------------------------
CREATE PROCEDURE HienThiLoaiVanChuyen
AS
	SELECT *
	FROM dbo.HinhThucVanChuyen
GO

CREATE PROCEDURE DangTinBanSanPham
@MaSanPham CHAR(10),
@TenSanPham NVARCHAR(100),
@TinhTrang NVARCHAR(30),
@XuatXu NVARCHAR(30),
@MoTaChiTiet NVARCHAR(200),
@LoaiSanPham NVARCHAR(30),
@MaTinBan CHAR(10), 
@MaNguoiBan CHAR(10),
@TieuDeTinBan NVARCHAR(50), 
@AnhDaiDien NVARCHAR(100), 
@ThoiGianBan Datetime, 
@GiaBan float, 
@TinhTrangTinBan NVARCHAR(30), 
@HinhThucBan NVARCHAR(50), 
@LoaiVanChuyen NVARCHAR(30)
AS
	-- them san pham vao he thong
	Insert Into SanPham (MaSanPham, TenSanPham, TinhTrang, XuatXu, MoTaChiTiet, LoaiSanPham) 
	Values (@MaSanPham, @TenSanPham, @TinhTrang, @XuatXu, @MoTaChiTiet, @LoaiSanPham)
	SET @MaSanPham = (select top 1 MaSanPham from dbo.SanPham order by MaSanPham DESC)
	-- them tin ban vao he thong
	Insert Into TinBan (MaTinBan, MaSanPham, MaNguoiBan, TieuDeTinBan, AnhDaiDien, ThoiGianBan, GiaBan, TinhTrangTinBan, HinhThucBan, LoaiVanChuyen)
	Values (@MaTinBan, @MaSanPham, @MaNguoiBan, @TieuDeTinBan, @AnhDaiDien, @ThoiGianBan, @GiaBan, @TinhTrangTinBan, @HinhThucBan, @LoaiVanChuyen)
GO

ALTER PROCEDURE ThemKhachHang
@MaKhachHang CHAR(10),
@TenTaiKhoan VARCHAR(20),
@MatKhau VARCHAR(20),
@CMND VARCHAR(15),
@HoTen NVARCHAR(100),
@NgaySinh DATETIME,
@GioiTinh NVARCHAR(5),
@DiaChi NVARCHAR(100),
@Email VARCHAR(100),
@DienThoai VARCHAR(15),
@DiemUyTin int
AS
	INSERT INTO dbo.KhachHang
	        ( MaKhachHang ,
	          TenTaiKhoan ,
	          MatKhau ,
	          CMND ,
	          HoTen ,
	          NgaySinh ,
	          GioiTinh ,
	          DiaChi ,
	          Email ,
	          DienThoai ,
	          TaiKhoanNganLuong ,
	          DiemUyTin
	        )
	VALUES  ( @MaKhachHang , -- MaKhachHang - char(10)
	          @TenTaiKhoan , -- TenTaiKhoan - varchar(20)
	          @MatKhau , -- MatKhau - varchar(20)
	          @CMND, -- CMND - varchar(15)
	          @HoTen , -- HoTen - nvarchar(100)
	          @NgaySinh , -- NgaySinh - datetime
	          @GioiTinh , -- GioiTinh - nvarchar(5)
	          @DiaChi , -- DiaChi - nvarchar(100)
	          @Email , -- Email - varchar(100)
	          @DienThoai , -- DienThoai - varchar(15)
	          null , -- TaiKhoanNganLuong - char(10)
	          @DiemUyTin  -- DiemUyTin - int
	        )
GO

---- update tên loại cho có ý nghĩa
UPDATE dbo.SanPham SET LoaiSanPham = N'Loại 1' WHERE MaSanPham LIKE N'%0'
UPDATE dbo.SanPham SET LoaiSanPham = N'Loại 2' WHERE MaSanPham LIKE N'%6'
UPDATE dbo.SanPham SET LoaiSanPham = N'Loại 3' WHERE MaSanPham LIKE N'%5'
---- update tình trạng tin bán cho có ý nghĩa
UPDATE dbo.TinBan SET TinhTrangTinBan = N'Còn hạn'


SELECT * FROM dbo.KhachHang WHERE MaKhachHang = 'KH00000003'
--SELECT * FROM dbo.SanPham WHERE MaSanPham = 'SP00000000'

--SELECT * FROM dbo.TinBan WHERE MaTinBan = 'TB00000000'

--SELECT * FROM DBO.DonHang WHERE MaDonHang = 'DH00000002'

